﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OracleClient;
using System.Data;
using System.Collections;
using System.Xml;
using Aspose.Words;
using Maticsoft.DBUtility;

namespace SkWeb.FASJ
{
    public partial class FASJ : System.Web.UI.Page
    {

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {

            }
        }


        protected void btnUplaod_Click(object sender, EventArgs e)
        {

            if (FileUpload1.HasFile)
            {
                //判断文件是否小于10Mb  
                if (FileUpload1.PostedFile.ContentLength < 10485760)
                {
                    try
                    {
                        FileUpload1.PostedFile.SaveAs(Server.MapPath("~/Files/")
                            + FileUpload1.FileName);
                        hiddenPath.Value = Server.MapPath("~/Files/") + FileUpload1.FileName;

                        lblMessage.Text = "上传成功!";

                        this.GridView1.DataSource = GetBookMarksFromDoc(Server.MapPath("~/Files/")
                            + FileUpload1.FileName);
                        this.GridView1.DataBind();
                        this.ddlTbName.DataSource = DtDropList;
                        ddlTbName.DataTextField = "TABLE_NAME";
                        ddlTbName.DataBind();
                        ddlTbName.Items.Insert(0, new ListItem(" "));
                        this.ddlTbName.SelectedIndexChanged += new EventHandler(ddlTbName_SelectedIndexChanged);

                       
                    }
                    catch (Exception ex)
                    {
                        lblMessage.Text = ex.Message;
                    }
                }
                else
                {
                    lblMessage.Text = "上传文件不能大于10MB!";
                }
            }
            else
            {
                lblMessage.Text = "尚未选择文件!";
            }
        }


        List<MyBookMark> GetBookMarksFromDoc(string fileName)
        {
            List<MyBookMark> bookMarks = new List<MyBookMark>();

            try
            {
                bookMarks = Tools.GetBookMarksFromDoc(fileName);
            }
            catch (Exception ex)
            {
                lblMessage.Text = ex.Message;
            }
            return bookMarks;
        }

        DataTable dtDropList = null;
        DataTable DtDropList
        {
            get
            {
                if (dtDropList == null)
                {
                    dtDropList = GetTableNames();
                    
                }
                return dtDropList;
            }
            set
            {
                dtDropList = value;
            }
        }

        DataTable dtDropList1 = null;
        DataTable DtDropList1
        {
            get
            {
                if (dtDropList1 == null)
                {
                    dtDropList1 = GetFormulas();
                }
                return dtDropList1;
            }
            set
            {
                dtDropList1 = value;
            }
        }
        DataTable GetFormulas()
        {
            string strSql = "select NAME from tb_formula ";
            DataTable dt = OracleHelper.Query(strSql).Tables[0];
            return dt;
        }

        DataTable GetTableNames()
        {//select a.table_name as tablename from user_tab_cols a   where a.column_name='qzh'
            // select  table_name||'  ('||comments||')' from  user_tab_comments where table_type='TABLE'
            string strSql = " select  table_name||'  ('||comments||')' table_name from  user_tab_comments where table_type='TABLE' and table_name in "
                + " (select a.table_name as tablename from user_tab_cols a   where a.column_name='ID')";
            DataTable dt = OracleHelper.Query(strSql).Tables[0];

            foreach (DataRow dr in dt.Rows)
            {
                dr["table_name"] = dr["table_name"].ToString().Replace("()", "");
            }
            dt.AcceptChanges();
            return dt;
        }




        protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                DropDownList ddlTableName = e.Row.FindControl("ddlTableName") as DropDownList;
                ddlTableName.DataSource = DtDropList;
                ddlTableName.DataTextField = "TABLE_NAME";
                ddlTableName.DataBind();
                ddlTableName.Items.Insert(0, new ListItem(" "));

                ddlTableName = e.Row.FindControl("ddlFormula") as DropDownList;
                ddlTableName.DataSource = DtDropList1;
                ddlTableName.DataTextField = "NAME";
                ddlTableName.DataBind();
                ddlTableName.Items.Insert(0, new ListItem(" "));
            }
        }


        protected void ddlTableName_SelectedIndexChanged(object sender, EventArgs e)
        {
            DropDownList ddlTableNames = sender as DropDownList;
            if (ddlTableNames.SelectedIndex == 0)
                return;

            string tableName = ddlTableNames.SelectedItem.Text.Trim();
            if (tableName.Contains('('))
            tableName = tableName.Remove(tableName.IndexOf('(')).Trim();
            string strSql = " select column_name||'  ('||comments||')' COLUMN_NAME from  user_col_comments t where table_name='" + tableName + "'";
            DataTable dtFieldNames = new DataTable();
            dtFieldNames = OracleHelper.Query(strSql).Tables[0];
            GridViewRow row = (GridViewRow)ddlTableNames.NamingContainer;
            DropDownList ddlFieldName = row.FindControl("ddlFieldName") as DropDownList;
            
            ddlFieldName.DataSource = dtFieldNames;
            ddlFieldName.DataTextField = "COLUMN_NAME";
            ddlFieldName.DataBind();
            ddlFieldName.Items.Insert(0, new ListItem(" "));
            foreach (ListItem item in ddlFieldName.Items)
            {
                item.Text = item.Text.Replace("()", "");
            }

            ddlFieldName = row.FindControl("ddlPkFieldName") as DropDownList;
            ddlFieldName.DataSource = dtFieldNames;
            ddlFieldName.DataTextField = "COLUMN_NAME";
            ddlFieldName.DataBind();
            ddlFieldName.Items.Insert(0, new ListItem(" "));
            foreach (ListItem item in ddlFieldName.Items)
            {
                item.Text = item.Text.Replace("()", "");
            }

        }
        protected void ddlTbName_SelectedIndexChanged(object sender, EventArgs e)//主键表选项发生变化时
        {
            DropDownList ddlTableNames = sender as DropDownList;
            if (ddlTableNames.SelectedIndex == 0)
                return;
            string tableName = ddlTableNames.SelectedItem.Text;
            string strSql = " select column_name||'  ('||comments||')' COLUMN_NAME from  user_col_comments t where table_name='" + tableName + "'";
            DataTable dtFieldNames = new DataTable();
            dtFieldNames = OracleHelper.Query(strSql).Tables[0];
            this.ddlFieldName1.DataSource = dtFieldNames;
            this.ddlFieldName1.DataTextField = "COLUMN_NAME";
            this.ddlFieldName1.DataBind();
            this.ddlFieldName1.Items.Insert(0, new ListItem(" "));

        }

        protected void btnSave_Click(object sender, EventArgs e)
        {
            string faName = txtSjName.Text.Trim();
            string filepath = hiddenPath.Value;
            string sjr = "";
            string pktablename = this.ddlTbName.Text;
            string pkfield = "ID";//this.ddlFieldName1.Text;
            //DateTime dtNow = DateTime.Now;
            string strSql = "insert into tb_sjfa(name,filepath,sjr,sjrq,pktablename,pkfield) "
                            + "values('" + faName + "','" + filepath + "','" + sjr + "', sysdate ,'" + pktablename + "','" + pkfield + "') ";
            OracleHelper.ExecuteSql(strSql);

            strSql = "select tb_sjfa_seq.currval from dual ";
            int faID = Convert.ToInt32(OracleHelper.Query(strSql).Tables[0].Rows[0][0].ToString());

            ArrayList strsSql = new ArrayList();

            foreach (GridViewRow dr in GridView1.Rows)
            {
                if (dr.RowType == DataControlRowType.DataRow)
                {
                    BMModel bmModel = new BMModel();
                    //bmModel.faid = faID;//方案id
                    bmModel.name = dr.Cells[0].Text;//书签名
                    bmModel.text = dr.Cells[1].Text;//书签文字
                    bmModel.tablename = (dr.FindControl("ddlTableName") as DropDownList).Text.Trim();
                    bmModel.pkfieldname = (dr.Cells[3].FindControl("ddlPkFieldName") as DropDownList).Text.Trim();
                    bmModel.fieldname = (dr.Cells[4].FindControl("ddlFieldName") as DropDownList).Text.Trim();
                    if (bmModel.tablename.Contains('('))
                    bmModel.tablename = bmModel.tablename.Remove(bmModel.tablename.IndexOf('（') ).Trim();
                    if (bmModel.pkfieldname.Contains('('))
                    bmModel.pkfieldname = bmModel.pkfieldname.Remove(bmModel.pkfieldname.IndexOf('（')).Trim();
                    if (bmModel.fieldname.Contains('('))
                    bmModel.fieldname = bmModel.fieldname.Remove(bmModel.fieldname.IndexOf('（')).Trim();
                    //bmModel.pkfieldname ;
                    //bmModel.fieldname ; 

                    bmModel.formula = (dr.Cells[5].FindControl("ddlFormula") as DropDownList).Text;
                    strSql = "insert into tb_bookmark(faid,name,text,tablename,pkfield,fieldname,formula) values(" + faID + ",'" + bmModel.name + "','" + bmModel.text + "','" + bmModel.tablename + "','" + bmModel.pkfieldname + "','" + bmModel.fieldname + "','" + bmModel.formula + "') ";
                    strsSql.Add(strSql);
                }
            }
            OracleHelper.ExecuteSqlTran(strsSql);
            lblMessage.Text = "保存成功！";
        }

        protected void GridView1_DataBound(object sender, EventArgs e)
        {
            //GridView1.Rows
        }

    }

    public class BMModel
    {
        public int id;
        public int faid;
        public string name;
        public string text;
        public string tablename;
        public string fieldname;
        public string pkfieldname;
        public string formula;
    }


    public class MyBookMark
    {
        string name = "";
        string text = "";
        public MyBookMark(string markName, string markText)
        {
            name = markName;
            text = markText;
        }
        public string Name
        {
            set { name = value; }
            get { return name; }
        }
        public string Text
        {
            set { text = value; }
            get { return text; }
        }
    }

    public class Tools
    {

        public static List<MyBookMark> GetBookMarksFromDoc(string filePath)
        {
            List<MyBookMark> bookMarks = new List<MyBookMark>();

            Document tempDocument;
            tempDocument = new Document(filePath);
            foreach (Bookmark bookMark in tempDocument.Range.Bookmarks)
            {
                if (bookMark.Name[0] != '_')
                    bookMarks.Add(new MyBookMark(bookMark.Name, bookMark.Text));

            }
            return bookMarks;

        }
        public static void FillDropDownList(DropDownList dropList, List<string> strList)
        {
            foreach (string str in strList)
            {
                dropList.Items.Add(str);
            }
        }
    }
}